#!/usr/bin/env python
# coding:utf-8

import pymysql, sys

# reload(sys)
# sys.setdefaultencoding('utf8')

# sql=sys.argv[1]

def select_mysql(sql):
    conn = pymysql.connect(host='121.201.72.168', port=56033, user='daxmall_r', passwd='2hGUnCKoEuvkWNgRbvwo',
                           db='daxmall',
                           charset='UTF8')
    # conn = pymysql.connect(host='10.0.0.141', user='daxmall_r', passwd='2hGUnCKoEuvkWNgRbvwo', db='daxmall',
    #                        charset='UTF8')

    cur = conn.cursor()
    reCount = cur.execute(sql)

    nRet = cur.fetchall()
    cur.close()

    conn.close()
    return nRet


def insert_mysql(sql):
    conn = pymysql.connect(host='121.201.68.21', port=3307, user='jiang', passwd='jiangwenhui', db='daxiangzhanshi',

                           charset='UTF8')

    cur = conn.cursor()

    cur.execute(sql)
    nRet = cur.fetchall()
    conn.commit()

    cur.close()

    conn.close()
    return nRet

if __name__ == "__main__":
    #select_ls_category = "SELECT r.province_id AS 省份ID,a.province 省份,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.total) AS 订单总金额 FROM ls_sub s INNER JOIN ls_usr_addr_sub a ON a.`addr_order_id`=s.`addr_order_sn`  INNER JOIN ls_usr_addr r ON r.`addr_id`=a.`addr_id`  WHERE s.update_date>=curdate()  AND s.status != 5 GROUP BY r.province_id order by SUM(s.total) desc "
    #select="SELECT r.province_id AS 省份ID,a.province 省份,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.total) as 订单总金额 FROM ls_sub s INNER JOIN ls_usr_addr_sub a ON a.`addr_order_sn` = s.`addr_order_sn` INNER JOIN ls_usr_addr r ON r.`addr_id` = a.`addr_id` WHERE s.update_date >= curdate() and s.status > 1 AND s. STATUS != 5 GROUP BY r.province_id ORDER BY SUM(s.total) DESC"
    #select="SELECT r.province_id AS 省份ID,a.province 省份,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.actual_total) as 订单总金额 FROM ls_sub s INNER JOIN ls_usr_addr_sub a ON a.`addr_order_sn` = s.`addr_order_sn` INNER JOIN ls_usr_addr r ON r.`addr_id` = a.`addr_id` WHERE s.update_date >= curdate() and s.status > 1 AND s. STATUS != 5 GROUP BY r.province_id ORDER BY SUM(s.total) DESC"
    #select="SELECT r.province_id AS 省份ID,a.province 省份,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.actual_total) as 订单总金额 FROM ls_sub s INNER JOIN ls_usr_addr_sub a ON a.`addr_order_sn` = s.`addr_order_sn` INNER JOIN ls_usr_addr r ON r.`addr_id` = a.`addr_id` WHERE s.pay_date >= curdate() and s.status > 1 AND s. STATUS != 5 GROUP BY r.province_id ORDER BY SUM(s.total) DESC;"
    select="SELECT r.province_id AS 省份ID, a.province 省份, round(COUNT(1),0) AS 订单数量, round(SUM(lst.product_nums)*1,0) AS 商品数量,	SUM(lst.cash_amount*1) as 订单总金额  FROM (	SELECT 	t.cash_amount,	t.sub_settlement_sn, sum(ls.product_nums) product_nums,	ls.addr_order_sn	FROM	ls_sub_settlement t,	ls_sub ls WHERE	t.clearing_time > curdate()	AND t.is_clearing = 1	AND t.flow_trade_no IS NOT NULL	AND t.sub_settlement_sn = ls.sub_settlement_sn GROUP BY t.sub_settlement_sn,t.cash_amount,ls.addr_order_sn	) lst,	ls_usr_addr_sub a,	ls_usr_addr r	WHERE	a.`addr_order_sn` = lst.addr_order_sn	AND r.`addr_id` = a.`addr_id`	GROUP BY	r.province_id	ORDER BY	SUM(lst.cash_amount) DESC;"
    data = select_mysql(select)
    insert_mysql("delete from jia_provinces_rmb")
    for i in data:
#	print i
        insert_ls_category = '''insert into jia_provinces_rmb (name,rmb,updatetime)values ('%s','%s',%s) '''%(i[1],float(i[4])*5,'curdate()')
        insert_ls_category=insert_ls_category.replace("市",'')
        insert_ls_category=insert_ls_category.replace("省",'')
        insert_mysql(insert_ls_category)
    #print(insert_ls_category)
